package unilearn.da;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Vector;

import unilearn.domain.Submission;

/**
 * @author Ben Morall, 4158768
 * @version 0.1, 24 April 2008, inital creation, blm
 */
public class DASubmission extends DABase
{
	private static final String SQL_CREATE_SUBMISSION = "INSERT INTO submission (assignment_id, date_submitted, file_id, studentNumber) VALUES (?,?,?,?)";

	private static final String SQL_DELETE_SUBMISSION = "DELETE FROM submission WHERE submission_id = ?";

	private static final String SQL_GET_SUBMISSION = "SELECT * FROM submission WHERE submission_id = ?"; // TODO

	private static final String SQL_GET_STUDENT_SUBMISSION = "SELECT s.* FROM submission s, Assignment a, Subject j"
			+ " WHERE a.assignment_id = s.assignment_id AND j.subject_id = a.subject_id AND s.student_number = ? ORDER BY j.subject_code, a.assignment_name, s.student_number"; // TODO

	private static final String SQL_GET_STUDENT_NUMBERS = "SELECT student_number FROM submission GROUP BY student_number ORDER BY student_number"; // TODO

	private static final String SQL_GET_SUBMISSION_UNGRADED = "TODO"; // TODO

	private static final String SQL_GET_ASSESSMENT_SUBMISSION = "SELECT s.* FROM submission s, assessment a WHERE a.submission_id = s.submission_id AND a.assessment_id = ?"; // TODO

	private static final String SQL_GET_ASSIGNMENT_SUBMISSIONS = "SELECT * FROM submission WHERE assignment_id = ?";

	private static final String SQL_UPDATE_SUBMISSION = "UPDATE submission SET date_submitted = ?, file_id = ? WHERE submission_id = ?";

	/** Creates a new instance of DASubmission */
	private DASubmission()
	{
	}

	private static Date getNow()
	{
		return new Date(new java.util.Date().getTime());
	}

	public static Submission createNewSubmission(long assignmentID, String studentNumber,
			long fileID) throws SQLException
	{
		if (studentNumber == null) { throw new NullPointerException("studentNumber"); }
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		Date date = getNow();
		Submission sub = null;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_CREATE_SUBMISSION, Statement.RETURN_GENERATED_KEYS);
		try
		{
			/* Generate Submission */
			stmt.setLong(1, assignmentID);
			stmt.setDate(2, date);
			stmt.setLong(3, fileID);
			stmt.setString(4, studentNumber);
			stmt.execute();

			/* Get ID */
			rs = stmt.getGeneratedKeys();
			if (rs == null) { throw new NullPointerException("rs"); }

			/* Return Record */
			if (rs.next())
			{
				sub = new Submission(rs.getLong(1));
				sub.setAssignmentID(assignmentID);
				sub.setDateSubmitted(date);
				sub.setFileID(fileID);
				sub.setStudentNumber(studentNumber);
			}
			else
			{
				throw new RuntimeException("Submission not created");
			}

			return sub;
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static boolean deleteSubmission(long submissionID) throws SQLException
	{
		Connection conn;
		PreparedStatement stmt;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_DELETE_SUBMISSION);
		try
		{
			stmt.setLong(1, submissionID);
			return stmt.execute();
		}
		finally
		{
			stmt.close();
		}
	}

	/**
	 * Generates a Submission from statements in a ResultSet
	 * @param rs ResultSet containing a Submission
	 * @return a Submission from <code>rs</code>
	 */
	private static Submission generateSubmission(ResultSet rs) throws SQLException
	{
		Submission sub = new Submission(rs.getLong("submission_id"));
		sub.setAssignmentID(rs.getLong("assignment_id"));
		sub.setDateSubmitted(rs.getDate("date_submitted"));
		sub.setFileID(rs.getLong("file_id"));
		sub.setStudentNumber(rs.getString("student_number"));
		return sub;
	}

	public static Submission getSubmission(long submissionID) throws SQLException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_SUBMISSION);
		try
		{
			/* Generate ResultSet */
			stmt.setLong(1, submissionID);
			rs = stmt.executeQuery();

			/* Return Record */
			if (!rs.next())
			{
				/* No Records Found */
				return null;
			}
			else
			{
				return generateSubmission(rs);
			}
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static Submission[] getStudentSubmissions(String studentNumber)
		throws SQLException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		Vector<Submission> subList;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_STUDENT_SUBMISSION);
		try
		{
			/* Generate ResultSet */
			stmt.setString(1, studentNumber);
			rs = stmt.executeQuery();

			/* Return Records */
			subList = new Vector<Submission>();
			while (rs.next())
			{
				subList.add(generateSubmission(rs));
			}
			return subList.toArray(new Submission[0]);
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static String[] getStudentNumberss() throws SQLException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		Vector<String> subList;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_STUDENT_NUMBERS);
		try
		{
			/* Generate ResultSet */
			rs = stmt.executeQuery();

			/* Return Records */
			subList = new Vector<String>();
			while (rs.next())
			{
				subList.add(rs.getString("student_number"));
			}
			return subList.toArray(new String[0]);
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}

	}

	public static Submission getAssessmentSubmission(long assessmentID) throws SQLException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_ASSESSMENT_SUBMISSION);
		try
		{
			/* Generate ResultSet */
			stmt.setLong(1, assessmentID);
			rs = stmt.executeQuery();

			/* Return Record */
			if (!rs.next())
			{
				/* No Records Found */
				return null;
			}
			else
			{
				return generateSubmission(rs);
			}
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static Submission[] getAssignmentSubmissions(long assignmentID)
		throws SQLException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		List<Submission> subList;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_ASSIGNMENT_SUBMISSIONS);
		try
		{
			/* Generate ResultSet */
			stmt.setLong(1, assignmentID);
			rs = stmt.executeQuery();

			/* Return Records */
			subList = new LinkedList<Submission>();
			while (rs.next())
			{
				subList.add(generateSubmission(rs));
			}
			return subList.toArray(new Submission[0]);
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static Submission[] getUngradedSubmissions(long assignmentID) throws SQLException
	{
		Connection conn;
		PreparedStatement stmt;
		ResultSet rs = null;
		List<Submission> subList;

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_GET_SUBMISSION_UNGRADED);
		try
		{
			/* Generate ResultSet */
			stmt.setLong(1, assignmentID);
			rs = stmt.executeQuery();

			/* Return Records */
			subList = new LinkedList<Submission>();
			while (rs.next())
			{
				subList.add(generateSubmission(rs));
			}
			return subList.toArray(new Submission[0]);
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			stmt.close();
		}
	}

	public static void updateSubmission(long submissionID, long fileID) throws SQLException
	{
		Connection conn;
		PreparedStatement stmt;
		Date date = getNow();

		conn = DABase.getConn();
		stmt = conn.prepareStatement(SQL_UPDATE_SUBMISSION);
		try
		{
			/* Generate ResultSet */
			stmt.setDate(1, date);
			stmt.setLong(2, fileID);
			stmt.setLong(3, submissionID);
			stmt.execute();
		}
		finally
		{
			stmt.close();
		}
	}
}
